<?php
namespace Biz\pockets\excel;

use PHPExcel_Reader_Excel5;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Cell;
use PHPExcel_RichText;
class ReadExcel
{
    private $phpReader;
    private $file;
    private $filePath='';

    public function __construct($file)
    {
        $this->file=$file;
        $exts=$this->getExt();
        if($exts=='xls'){
            $this->phpReader=new PHPExcel_Reader_Excel5();
        }else if($exts=='xlsx'){
            $this->phpReader=new PHPExcel_Reader_Excel2007();
        }else{
            throw new \Exception('后缀名不是标准格式');
        }
    }

    /**
     * $header_mapping 文字和数据库字段的对应关系
     * excel第一行 是 字段标准(通常是汉字)，
     * example
     * $header_mapping = [ '姓名' => 'uid' ];
     * !!该方法会过滤为null的数据!!!
     */
    public function getExcelData($sheetIndex = 0,$header_mapping = [], $isfilter = false,$startIndex=1)
    {
        $objPHPExcel=$this->phpReader->load($this->filePath.$this->file['tmp_name']);
        $sheet = $objPHPExcel->getSheet($sheetIndex);

        //获取行数与列数,注意列数需要转换
        $highestRowNum = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $highestColumnNum = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $usefullColumnNum = $highestColumnNum;

        //取得字段，这里测试表格中的第一行为数据的字段，因此先取出用来作后面数组的键名
        $filed = array();
        for($i=0; $i<$highestColumnNum;$i++){
            $cellName = PHPExcel_Cell::stringFromColumnIndex($i).$startIndex;
            $cellVal = $sheet->getCell($cellName)->getValue();//取得列内容
            if( !$cellVal ){
                break;
            }
            $usefullColumnNum = $i;
            $filed []= $cellVal;
        }

        //开始取出数据并存入数组
        $data = [];
        for( $i=$startIndex+1; $i <= $highestRowNum ;$i++ ){//ignore row 1
            $row = array();
            for( $j = 0; $j <= $usefullColumnNum;$j++ ){
                if( !isset($header_mapping[ $filed[$j] ]) ){
                    continue;
                }
                $cellName = PHPExcel_Cell::stringFromColumnIndex($j).$i;
                $cellVal = $sheet->getCell($cellName)->getValue();
                if($cellVal instanceof PHPExcel_RichText){ //富文本转换字符串
                    $cellVal = $cellVal->__toString();
                }

                $fd = $header_mapping[ $filed[$j] ];
                $row[ $fd ] = $cellVal;
            }

            if($isfilter) {
                if(count(array_filter($row)) == $usefullColumnNum + 1) {
                    $data []= $row;
                }
            } else {
                $data []= $row;
            }
        }

        //@unlink ( $this->filePath.$this->file['tmp_name'] ); //删除上传的文件

        return $data;
    }

    /**
     * 获取文件后缀名
     * @return bool|string
     * @throws \Exception
     */
    private function getExt()
    {
        $pos=mb_strrpos($this->file['name'],'.');
        if($pos === false){
            throw new \Exception('请检查文件后缀名是否配置');
        }

        return mb_substr($this->file['name'],$pos+1);
    }
}